﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//------------//
using QLKTX1._0.DTO;
using QLKTX1._0.DB;
using System.Data.SqlClient;
using System.Data;
using System.Globalization;
//....................
namespace QLKTX1._0.DAO
{
    class SinhVienDAO
    {
        public static SinhVienDTO getInfoSinhVien(String ID)
        {
            SinhVienDTO sv = new SinhVienDTO();

            ConnectDB con = new ConnectDB();

            String sql="";
            sql+=" SELECT dbo.SinhVien.maSoTheNoiTru, dbo.SinhVien.maSinhVien, dbo.SinhVien.hoVaTen, dbo.SinhVien.cMND, dbo.TonGiao.maTonGiao, dbo.TonGiao.tenTonGiao, ";
            sql+=" dbo.DanToc.tenDanToc, dbo.DanToc.maDanToc, dbo.Khoa.maKhoa, dbo.Khoa.tenKhoa, dbo.Lop.maLop, dbo.Lop.tenLop, dbo.QuanHe.maQuanHe, ";
            sql+=" dbo.QuanHe.tenQuanHe, dbo.SinhVien.gioiTinh, dbo.SinhVien.queQuan, dbo.SinhVien.ngaySinh, dbo.SinhVien.dienThoaiSV, dbo.SinhVien.ngayDangKyKTX, ";
            sql+=" dbo.SinhVien.hinhAnh, dbo.SinhVien.tenNhanThan, dbo.SinhVien.diaChiNhanThan, dbo.SinhVien.dienThoaiNhanThan, dbo.SinhVien.ngheNghiep, dbo.namHoc.maNamHoc ,dbo.namHoc.tenNamHoc ";
            sql+=" FROM  dbo.SinhVien INNER JOIN";
            sql+=" dbo.TonGiao ON dbo.SinhVien.maTonGiao_FK = dbo.TonGiao.maTonGiao INNER JOIN";
            sql+=" dbo.DanToc ON dbo.SinhVien.maDanToc_FK = dbo.DanToc.maDanToc INNER JOIN";
            sql+=" dbo.Khoa ON dbo.SinhVien.maKhoa_FK = dbo.Khoa.maKhoa INNER JOIN";
            sql+=" dbo.Lop ON dbo.SinhVien.maLop_FK = dbo.Lop.maLop INNER JOIN";
            sql+=" dbo.QuanHe ON dbo.SinhVien.maQuanHe_FK = dbo.QuanHe.maQuanHe INNER JOIN";
            sql+=" dbo.namHoc ON dbo.SinhVien.maNamHoc_FK = dbo.namHoc.maNamHoc";
            sql+=" WHERE (dbo.SinhVien.maSoTheNoiTru = '"+ID+"')";

            DataTable dt = con.getDataTable(sql);

            if (dt.Rows.Count == 1)
            {
                int i = 0; // luon luon co 1 hang 

                sv.MaSoTheNoiTru = dt.Rows[i]["maSoTheNoiTru"].ToString();

                sv.MaSinhVien = dt.Rows[i]["maSinhVien"].ToString();

                sv.HoVaTen = dt.Rows[i]["hoVaTen"].ToString().ToUpper();

                sv.CMND = dt.Rows[i]["cMND"].ToString();

                sv.HinhAnh = dt.Rows[i]["hinhAnh"].ToString();

                sv.DienThoai = dt.Rows[i]["dienThoaiSV"].ToString();

                sv.QueQuan = dt.Rows[i]["queQuan"].ToString();

                sv.NgaySinh = Convert.ToDateTime(dt.Rows[i]["ngaySinh"].ToString());

                sv.NgayDangKyKTX = Convert.ToDateTime(dt.Rows[i]["ngayDangKyKTX"].ToString());
               

                if (Convert.ToBoolean(dt.Rows[i]["gioiTinh"]))
                {
                    sv.GioiTinh = 1;
                }
                else sv.GioiTinh = 0;

                TonGiaoDTO tg = new TonGiaoDTO();
                tg.MaTonGiao = Int32.Parse(dt.Rows[i]["maTonGiao"].ToString());
                tg.TenTonGiao = dt.Rows[i]["tenTonGiao"].ToString();
                sv.MaTonGiao = tg;

                DanTocDTO t = new DanTocDTO();
                t.MaDanToc = Int32.Parse(dt.Rows[i]["maDanToc"].ToString());
                t.TenDanToc = dt.Rows[i]["tenDanToc"].ToString();
                sv.MaDanToc = t;

                LopDTO lop = new LopDTO();
                lop.MaLop = Int32.Parse(dt.Rows[i]["maLop"].ToString()); ;
                lop.TenLop = dt.Rows[i]["tenLop"].ToString(); ;
                sv.MaLop = lop;

                KhoaDTO khoa = new KhoaDTO();
                khoa.MaKhoa = Int32.Parse(dt.Rows[i]["maKhoa"].ToString());
                khoa.TenKhoa = dt.Rows[i]["tenKhoa"].ToString();
                sv.MaKhoa = khoa;
                NamHocDTO nh = new NamHocDTO();
                nh.MaNamHoc = Int32.Parse(dt.Rows[i]["maNamHoc"].ToString());
                nh.TenNamHoc = dt.Rows[i]["tenNamHoc"].ToString();
                sv.MaNamHoc = nh;

                // ------- nhan than ---------//

                sv.TenNhanThan = dt.Rows[i]["tenNhanThan"].ToString(); ;
                sv.DienThoaiNhanThan = dt.Rows[i]["dienThoaiNhanThan"].ToString(); ;
                sv.DiaChiNhanThan = dt.Rows[i]["diaChiNhanThan"].ToString(); ;
                sv.NgheNghiep = dt.Rows[i]["ngheNghiep"].ToString(); ;
                QuanHeDTO qh = new QuanHeDTO();
                qh.MaQuanHe = Int32.Parse(dt.Rows[i]["maQuanHe"].ToString()); ;
                qh.TenQuanHe = dt.Rows[i]["tenQuanHe"].ToString();
                sv.MaQuanHe = qh;            

           
            }

            return sv;
        }
        public static List<SinhVienDTO> dsSinhVien()
        {
            List<SinhVienDTO> dsSinhVien = new List<SinhVienDTO>();

            ConnectDB con = new ConnectDB();
            DataTable dt = con.getDataTable("SELECT * FROM SinhVien");

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                SinhVienDTO sv = new SinhVienDTO();

                DanTocDTO t = new DanTocDTO();
                t.MaDanToc = Int32.Parse(dt.Rows[i]["maDanToc_FK"].ToString());

                LopDTO l = new LopDTO();
                l.MaLop = Int32.Parse(dt.Rows[i]["maLop_FK"].ToString());

                TonGiaoDTO tg = new TonGiaoDTO();
                tg.MaTonGiao = Int32.Parse(dt.Rows[i]["maTonGiao_FK"].ToString());

                KhoaDTO k = new KhoaDTO();
                k.MaKhoa = Int32.Parse(dt.Rows[i]["maKhoa_FK"].ToString());

                QuanHeDTO qh = new QuanHeDTO();
                qh.MaQuanHe = Int32.Parse(dt.Rows[i]["maQuanHe_FK"].ToString());

                NamHocDTO nh = new NamHocDTO();
                nh.MaNamHoc = Int32.Parse(dt.Rows[i]["maNamHoc_FK"].ToString());
               // nh.TenNamHoc = dt.Rows[i]["tenNamHoc"].ToString();
                sv.MaNamHoc = nh;

                sv.MaSoTheNoiTru = dt.Rows[i]["maSoTheNoiTru"].ToString();
                sv.MaSinhVien = dt.Rows[i]["maSinhVien"].ToString().ToUpper();
                sv.HoVaTen = dt.Rows[i]["hoVaTen"].ToString();
                sv.CMND =dt.Rows[i]["cMND"].ToString();                
                sv.QueQuan = dt.Rows[i]["queQuan"].ToString();
                sv.NgaySinh = DateTime.Parse(dt.Rows[i]["ngaySinh"].ToString());
                sv.DienThoai = dt.Rows[i]["dienThoaiSV"].ToString();
                sv.NgayDangKyKTX = DateTime.Parse(dt.Rows[i]["ngayDangKyKTX"].ToString());
                sv.MaDanToc = t;
                sv.MaLop = l;
                sv.MaTonGiao = tg;
                sv.MaKhoa = k;
                sv.HinhAnh = dt.Rows[i]["hinhAnh"].ToString();
                sv.TenNhanThan = dt.Rows[i]["tenNhanThan"].ToString();
                sv.DiaChiNhanThan = dt.Rows[i]["diaChiNhanThan"].ToString();
                sv.DienThoaiNhanThan = dt.Rows[i]["dienThoaiNhanThan"].ToString();
                sv.NgheNghiep = dt.Rows[i]["ngheNghiep"].ToString();
                sv.MaQuanHe = qh;


                dsSinhVien.Add(sv);
            }
            return dsSinhVien;
        }
        //Ham Insert
        public static bool themSinhVien(SinhVienDTO sv)
        {
            ConnectDB con = new ConnectDB();// dung DB

            String sql = "INSERT INTO SinhVien(maSoTheNoiTru,maSinhVien,hoVaTen,cMND,gioiTinh,queQuan,ngaySinh,dienThoaiSV,ngayDangKyKTX,maDanToc_FK,maLop_FK,maTonGiao_FK,maKhoa_FK,hinhAnh,tenNhanThan,diaChiNhanThan,dienThoaiNhanThan,ngheNghiep,maQuanHe_FK,maNamHoc_FK) VALUES('" + sv.MaSoTheNoiTru + "','" + sv.MaSinhVien + "',UPPER(N'" + sv.HoVaTen + "'),'" + sv.CMND + "'," + sv.GioiTinh + ",'" + sv.QueQuan + "','"+sv.NgaySinh.ToString("yyyy-MM-dd")+"','" + sv.DienThoai + "','"+sv.NgayDangKyKTX.ToString("yyyy-MM-dd")+"'," + sv.MaDanToc.MaDanToc + "," + sv.MaLop.MaLop + "," + sv.MaTonGiao.MaTonGiao + "," + sv.MaKhoa.MaKhoa + ",'"+sv.HinhAnh+"','" + sv.TenNhanThan + "','" + sv.DiaChiNhanThan + "','" + sv.DienThoaiNhanThan + "','" + sv.NgheNghiep + "'," + sv.MaQuanHe.MaQuanHe + ","+sv.MaNamHoc.MaNamHoc+")";

            return con.executeQuery(sql);
            
        }
        //Ham Delete
        public static bool xoaSinhVien(SinhVienDTO sv)
        {
            ConnectDB con = new ConnectDB();

            return con.executeQuery("DELETE FROM SinhVien WHERE maSoTheNoiTru='" + sv.MaSoTheNoiTru + "'");
        }
        //Ham Update
        public static bool capNhatSinhVien(SinhVienDTO sv)
        {
            ConnectDB con = new ConnectDB();

            return con.executeQuery("UPDATE SinhVien set maSinhVien='" + sv.MaSinhVien + "',hoVaTen='" + sv.HoVaTen + "',cMND='" + sv.CMND + "',gioiTinh=" + sv.GioiTinh + ",queQuan='" + sv.QueQuan + "',ngaySinh='" + sv.NgaySinh.ToString("yyyy-MM-dd") + "',dienThoaiSV='" + sv.DienThoai + "',ngayDangKyKTX='" + sv.NgayDangKyKTX.ToString("yyyy-MM-dd") + "',hinhAnh='" + sv.HinhAnh + "',tenNhanThan='" + sv.TenNhanThan + "',diaChiNhanThan='" + sv.DiaChiNhanThan + "',dienThoaiNhanThan='" + sv.DienThoaiNhanThan + "',ngheNghiep='" + sv.NgheNghiep + "',maNamHoc_Fk="+sv.MaNamHoc.MaNamHoc+",maDanToc_FK="+sv.MaDanToc.MaDanToc+"  WHERE maSoTheNoiTru='" + sv.MaSoTheNoiTru + "'");
        }
    }
}
